package date0704am;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
   -- MySQL :
   CREATE TABLE t_accounts (
   		id INT(5) ,
   		account VARCHAR(20) NOT NULL UNIQUE ,
   		balance FLOAT ,
   		name VARCHAR(30) ,
   		PRKMARY KEY( id )
   ) ;
   INSERT INTO t_accounts VALUES ( 1 , '100200300400' , 1000 , '张三丰' );
   INSERT INTO t_accounts VALUES ( 2 , '400200300100' , 2000 , '张三丰' );
   
   -- Oracle :
   CREATE TABLE t_accounts (
   		id NUMBER(5) ,
   		account VARCHAR2(20) NOT NULL UNIQUE ,
   		balance NUMBER(10,2) ,
   		name VARCHAR2(30) ,
   		PRKMARY KEY( id )
   ) ;
   INSERT INTO t_accounts VALUES ( 1 , '100200300400' , 1000 , '张三丰' );
   INSERT INTO t_accounts VALUES ( 2 , '400200300100' , 2000 , '张三丰' );
 */
public class JdbcTest21 {

	public static void main(String[] args) throws Exception {
		
//		String driver = "oracle.jdbc.driver.OracleDriver";
//		String url = "jdbc:oracle:thin:@localhost:1521:ecut";
//		String user = "ecuter";
//		String password = "ecuter";

		String driver = "com.mysql.cj.jdbc.Driver"  ;
		String url = "jdbc:mysql://localhost:3306/ecut?serverTimezone=Asia/Shanghai&useSSL=false" ;
		String user = "ecuter" ;
		String password = "ecuter" ;
		
		// 1、加载数据库驱动
		Class.forName( driver );
		// 2、获得数据库连接
		Connection connection = DriverManager.getConnection(url, user, password);
		
		System.out.println( "transaction isolation level : " + connection.getTransactionIsolation() ); 
		
		Statement statement = connection.createStatement() ;
		
		// 【 1、关闭自动提交】
		connection.setAutoCommit( false ); // autocommit 是针对 connection 的 、不是针对 事务的
		
		// 采用 commit 让一个新的事务开始 ( 同一个会话中一个事务终结另外一个新的事务立即开始 )
		connection.commit(); // 【开启一个新的事务】
		
		try {
			String reduce = "UPDATE t_accounts SET balance = balance - 500 WHERE account = '400200300100' " ;
			statement.executeUpdate( reduce );
			
			Thread.sleep( 10000 );
			
			System.out.println( 5 / 0  ); // 算术异常
			
			String grow = "UPDATE t_accounts SET balance = balance + 500 WHERE account = '100200300400' " ;
			statement.executeUpdate( grow );
			// 【 2、提交或回滚事务 】
			connection.commit();
		} catch ( Exception cause ) {
			// 【 2、提交或回滚事务 】
			connection.rollback();
			cause.printStackTrace();
		}
		
		// 【3、启用自动提交】
		connection.setAutoCommit( true );
		
		// 6、释放资源( 所有已经开启的资源必须都关闭 )
		statement.close();
		connection.close();

	}

}
